﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace DBUtility
{
  public class Sqlopenhander:DBopenhandler
    {

      static string constr = System.Configuration.ConfigurationManager.AppSettings["DBString"];
      public Sqlopenhander()
      {
          SqlConnection con = new SqlConnection(constr);
          conn = con;
          conn.Open();
          cmd = conn.CreateCommand();
          da=new SqlDataAdapter();
      }
      /// <summary>
      /// 产生SqlCommand对象所需的查询参数。override是重写的意思重写父类的构造函数
      /// cmd中的commandText（sql语句）中需要提供该参数。
      ///说白了就是给数据库语句中的参数赋值。
      ///cmd.Parameters.AddWithValue("@p_StationID", s_ID);给前面cmd添加一个名字为@p_StationID的参数，赋值为s_ID.
      /// </summary>
      public override void GenParameters()
      {
          SqlCommand sqlCmd = (SqlCommand)cmd;
          if (this.alFieldItems.Count > 0)
          {
              for (int i = 0; i < alFieldItems.Count; i++)
              {
                  sqlCmd.Parameters.AddWithValue("@para" + i.ToString(), ((DBkeyitem)alFieldItems[i]).fieldvalue.ToString());
                 
              }
          }
          if (this.alSqlCmdParameters.Count > 0)
          {
              for (int i = 0; i < this.alSqlCmdParameters.Count; i++)
              {
                  sqlCmd.Parameters.AddWithValue(((DBkeyitem)alSqlCmdParameters[i]).fieldname.ToString(), ((DBkeyitem)alSqlCmdParameters[i]).fieldvalue.ToString());
              }
          }
          if (this.alConditionParameters.Count > 0)
          {
              for (int i = 0; i < this.alConditionParameters.Count; i++)
              {
                  sqlCmd.Parameters.AddWithValue(((DBkeyitem)alConditionParameters[i]).fieldname.ToString(), ((DBkeyitem)alConditionParameters[i]).fieldvalue.ToString());
              }
          }
      }

      /// <summary>
      ///获取分页数据表
      /// </summary>
      /// <param name="TableName">表名</param>
      /// <param name="FieldKey"></param>
      /// <param name="PageCurrent">当前页</param>
      /// <param name="PageSize">一页的有多少条数据</param>
      /// <param name="FieldShow">列名</param>
      /// <param name="FieldOrder">排列顺序</param>
      /// <param name="Where">条件</param>
      /// <param name="RecordCount">有多少条数据</param>
      /// <returns></returns>
      /// SELECT 列名称 FROM 表名称
      /// limit是从什么地方开始取多少条数据
      public static DataTable QueryPagedDt(string TableName, string FieldKey, int PageCurrent, int PageSize, string FieldShow, string FieldOrder, string Where, ref int RecordCount)
      {
          if (FieldOrder != "")
          {
              FieldOrder = " order by " + FieldOrder;
          }
          //计算有多少的数目
          //string   sql   =   "select   count(*)   from   userinfo   where   uName= ' "+userName+ " ' " 
          string SqlTT = "select count(*) from " + TableName + " where " + Where;
          DataTable DtTT = QueryDt(SqlTT);
          int.TryParse(DtTT.Rows[0][0].ToString(), out RecordCount);

          if (PageCurrent < 1)
          {
              PageCurrent = 1;
          }
          int limit = (PageCurrent - 1) * PageSize;
          string Sql = "select  top " + PageSize + " *  from  " + TableName + "  where  " + FieldShow + "  not  in " + "(" + " select  top " + limit + " " + FieldShow + "  from " + TableName +" where "+ Where+FieldOrder+")" + " and " + Where + FieldOrder;
          DataTable Dt = QueryDt(Sql);
          return Dt;
      }
      /// <summary>
      /// 这个分页是用存储过程的内容进行快速的分页
      /// </summary>
      /// <param name="TableName"></param>
      /// <param name="FieldKey"></param>
      /// <param name="PageCurrent"></param>
      /// <param name="PageSize"></param>
      /// <param name="FieldShow"></param>
      /// <param name="FieldOrder"></param>
      /// <param name="Where"></param>
      /// <param name="RecordCount"></param>
      /// <returns></returns>
      public static DataTable QueryPagedDts(string TableName, string FieldKey, int PageCurrent, int PageSize, string FieldShow, string FieldOrder, string Where, ref int RecordCount)
      {
          string dbConnStr = constr;
          SqlConnection conn = new SqlConnection(dbConnStr);
          conn.Open();
          SqlDataAdapter cmd = new SqlDataAdapter("sp_PageView", conn);
          cmd.SelectCommand.CommandType = CommandType.StoredProcedure;
          SqlParameter[] pars = new SqlParameter[] { 
                new SqlParameter("@tbname",TableName),
                new SqlParameter("@FieldKey",FieldKey),
                new SqlParameter("@PageCurrent",PageCurrent),
                new SqlParameter("@PageSize",PageSize),
                new SqlParameter("@FieldShow",FieldShow),
                new SqlParameter("@FieldOrder",FieldOrder),
                new SqlParameter("@Where",Where),
                new SqlParameter("@RecordCount",RecordCount)
            };
          pars[7].Direction = ParameterDirection.Output;
          foreach (SqlParameter p in pars)
              cmd.SelectCommand.Parameters.Add(p);
          DataSet ds = new DataSet();
          cmd.Fill(ds);
          cmd.SelectCommand.Parameters.Clear();
          RecordCount = (int)pars[7].Value;
          conn.Close();

          return ds.Tables[0];
          #region
          /*
       USE [JHGS]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--字符串缓存实现的通用分页存储过程  
  
CREATE PROC [dbo].[sp_PageView]     
@tbname     sysname,               --要分页显示的表名     
@FieldKey   sysname,               --用于定位记录的主键(惟一键)字段,只能是单个字段     
@PageCurrent int=1,                 --要显示的页码     
@PageSize   int=10,                --每页的大小(记录数)     
@FieldShow  nvarchar(1000)='',      --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段     
@FieldOrder  nvarchar(1000)='',     --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC   用于指定排序顺序     
@Where     nvarchar(1000)='',      --查询条件     
@RecordCount  int OUTPUT            --总记录     
AS
DECLARE @sql nvarchar(4000)     
SET NOCOUNT ON     
--检查对象是否有效     
IF OBJECT_ID(@tbname) IS NULL     
BEGIN     
    RAISERROR(N'对象"%s"不存在',1,16,@tbname)     
    RETURN     
END     
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0     
    AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0     
    AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0     
BEGIN     
    RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)     
    RETURN     
END     
     
--分页字段检查     
IF ISNULL(@FieldKey,N'')=''     
BEGIN     
    RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)     
    RETURN     
END     
     
--其他参数检查及规范     
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1     
IF ISNULL(@PageSize,0)<1 SET @PageSize=10     
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'     
IF ISNULL(@FieldOrder,N'')=N''     
    SET @FieldOrder=N''     
ELSE     
    SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)     
IF ISNULL(@Where,N'')=N''     
    SET @Where=N''     
ELSE     
    SET @Where=N'WHERE ('+@Where+N')'     
     
  
SET @sql=N'SELECT @RecordCount=COUNT(*)'     
    +N' FROM '+@tbname     
    +N' '+@Where     
EXEC sp_executesql @sql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT     
       
  
  
if(@PageCurrent>1)  
BEGIN  
	if(@PageSize>100)
	BEGIN  
		set @PageSize=100
	END
END
     
--计算分页显示的TOPN值     
DECLARE @TopN varchar(20),@TopN1 varchar(20)     
SELECT @TopN=@PageSize,     
    @TopN1=@PageCurrent*@PageSize         
     
--第一页直接显示     
IF @PageCurrent=1     
    EXEC(N'SELECT TOP '+@TopN     
        +N' '+@FieldShow     
        +N' FROM '+@tbname     
        +N' '+@Where     
        +N' '+@FieldOrder)     
ELSE     
BEGIN     
    SELECT @PageCurrent=@TopN1,     
        @sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN     
            +N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey     
            +N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@tbname     
            +N' '+@Where     
            +N' '+@FieldOrder     
    SET ROWCOUNT @PageCurrent     

    EXEC sp_executesql @sql,     
        N'@n int,@s nvarchar(4000) OUTPUT',     
        @PageCurrent,@sql OUTPUT     
    SET ROWCOUNT 0     
    IF @sql=N''     
        EXEC(N'SELECT TOP 0'     
            +N' '+@FieldShow     
            +N' FROM '+@tbname)     
    ELSE     
    BEGIN     
        SET @sql=STUFF(@sql,1,1,N'')             
        --执行查询     
        EXEC(N'SELECT TOP '+@TopN     
            +N' '+@FieldShow     
            +N' FROM '+@tbname     
            +N' WHERE '+@FieldKey     
            +N' IN('+@sql     
            +N') '+@FieldOrder)     

    END     
END

GO



           
           */
          #endregion

      }
      public static void ExecuteSql(string Sql)
      {
          string dbConnStr = System.Configuration.ConfigurationSettings.AppSettings["DBString"];
          SqlConnection conn = new SqlConnection(dbConnStr);
          conn.Open();
          SqlCommand cmd = new SqlCommand(Sql, conn);
          cmd.ExecuteNonQuery();
          conn.Close();
      }
      /// <summary>
      ///从数据库中获取数据表
      /// </summary>
      public static DataTable QueryDt(string Sql)
      {
          SqlConnection conn = new SqlConnection(constr);
          conn.Open();
          SqlDataAdapter da = new SqlDataAdapter(Sql, conn);
          DataSet ds = new DataSet();
          da.Fill(ds);
          conn.Close();
          return ds.Tables[0];
      }
    }
}
